package com.kimo.shop.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.BooleanUtil;
import cn.hutool.core.util.PhoneUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.kimo.shop.bean.enums.DeliveryType;
import com.kimo.shop.bean.enums.DvyType;
import com.kimo.shop.bean.model.Delivery;
import com.kimo.shop.bean.model.Order;
import com.kimo.shop.bean.model.OrderItem;
import com.kimo.shop.bean.model.UserAddrOrder;
import com.kimo.shop.bean.param.OrderParam;
import com.kimo.shop.bean.vo.UnDeliveryOrderExcelVO;
import com.kimo.shop.common.exception.KimoShopBindException;
import com.kimo.shop.common.i18n.I18nMessage;
import com.kimo.shop.common.util.PoiExcelUtil;
import com.kimo.shop.dao.OrderMapper;
import com.kimo.shop.service.DeliveryService;
import com.kimo.shop.service.OrderExcelService;
import com.kimo.shop.service.OrderService;
import lombok.RequiredArgsConstructor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * @author kimo-taozhouchuan
 * @date 2021/7/21 15:37
 */
@Service
@RequiredArgsConstructor
public class OrderExcelServiceImpl extends ServiceImpl<OrderMapper, Order> implements OrderExcelService {

    @Value("${kimo.expose.permission:}")
    private Boolean permission;

    private final OrderMapper orderMapper;
    private final DeliveryService deliveryService;
    private final OrderService orderService;


    private final static int MIN_ROW = 2;
    private final static int FLOW_ID_MIN = 4;
    private final static int FLOW_ID_MAX = 30;

    @Override
    public void soldExcel(OrderParam orderParam, HttpServletResponse response) {
        List<Order> orders = orderMapper.listOrdersDetialByOrderInfo(orderParam);

        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getBigWriter();
        // 待发货
        String[] hearder = {"订单编号", "下单时间", "收件人", "手机", "收货地址", "商品名称", "数量", "订单应付", "订单运费", "订单实付", "订单状态", "售后状态"};
        Sheet sheet = writer.getSheet();
        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(3, 20 * 256);
        sheet.setColumnWidth(4, 60 * 256);
        sheet.setColumnWidth(5, 60 * 256);

        writer.merge(hearder.length - 1, "销售信息整理");
        writer.writeRow(Arrays.asList(hearder));

        int row = 1;
        for (Order dbOrder : orders) {
            UserAddrOrder addr = dbOrder.getUserAddrOrder();
            String addrInfo = "";
            if(Objects.nonNull(addr)){
                addrInfo = addr.getProvince() + addr.getCity() + addr.getArea() + addr.getAddr();
            }
            if(Objects.equals(dbOrder.getDvyType(), DvyType.STATION.value())){
                addrInfo = "自提订单";
            }
            List<OrderItem> orderItems = dbOrder.getOrderItems();
            int firstRow = row + 1;
            int lastRow = row + orderItems.size();
            int col = -1;
            // 订单编号
            mergeIfNeed(writer, firstRow, lastRow, ++col, col, dbOrder.getOrderNumber());
            // 下单时间
            mergeIfNeed(writer, firstRow, lastRow, ++col, col, dbOrder.getCreateTime());
            // 收件人
            mergeIfNeed(writer, firstRow, lastRow, ++col, col, dbOrder.getReceiverName());
            // "手机"
            mergeIfNeed(writer, firstRow, lastRow, ++col, col, BooleanUtil.isFalse(permission)? PhoneUtil.hideBetween(dbOrder.getReceiverMobile()).toString():dbOrder.getReceiverMobile());
            // "收货地址"
            mergeIfNeed(writer, firstRow, lastRow, ++col, col, addrInfo);
            int prodNameCol = ++col;
            int prodCountCol = ++col;
            for (OrderItem orderItem : orderItems) {
                row++;
                // 商品名称
                writer.writeCellValue(prodNameCol, row, orderItem.getProdName());
                // 数量
                writer.writeCellValue(prodCountCol, row, orderItem.getProdCount());
            }
            // 订单应付
            mergeIfNeed(writer, firstRow, lastRow, ++col, col, dbOrder.getTotal());
            // 订单运费
            mergeIfNeed(writer, firstRow, lastRow, ++col, col, dbOrder.getFreightAmount());
            // 订单实付
            mergeIfNeed(writer, firstRow, lastRow, ++col, col, dbOrder.getActualTotal());
            String status = "";
            switch (dbOrder.getStatus()){
                case 1: status = "待付款"; break;
                case 2: status = "待发货"; break;
                case 3: status = "待收货"; break;
                case 4: status = "待评价"; break;
                case 5: status = "成功"; break;
                case 6: status = "失败"; break;
                case 7: status = "待成团"; break;
                default: status = "";
            }
            String refundStatus = "";
            mergeIfNeed(writer, firstRow, lastRow, ++col, col, status);
            if (!Objects.isNull(dbOrder.getRefundStatus())){
                switch (dbOrder.getRefundStatus()){
                    case 1: refundStatus = "申请退款"; break;
                    case 2: refundStatus = "退款成功"; break;
                    case 3: refundStatus = "退款成功"; break;
                    case 4: refundStatus = "退款失败"; break;
                    default: break;
                }
            }else {
                refundStatus = "暂无售后";
            }
            mergeIfNeed(writer, firstRow, lastRow, ++col, col, refundStatus);
        }
        PoiExcelUtil.writeExcel(response, writer);
    }

    @Override
    public void unDeliveryOrderExcel(OrderParam orderParam, HttpServletResponse response) {
        orderParam.setLang(I18nMessage.getDbLang());
        List<UnDeliveryOrderExcelVO> orderExcelList = orderMapper.listUnDeliveryOrderExcel(orderParam);
        //创建writer
        ExcelWriter writer = ExcelUtil.getBigWriter();
        List<String> orderHeaderList;
        String[] orderHeaderCn = {"订单号", "配送方式*", "快递公司名称*", "快递单号*", "收货人姓名", "收货人手机号", "收货地址", "商品名称", "sku名称", "数量"};
        String[] orderHeaderEn = {"Order Number", "Delivery method", "Express company name", "Express delivery number", "Consignee's name", "Receiving Address"
                , "Product name", "Sku name", "Count"};
        orderHeaderList = Arrays.asList(Objects.equals(I18nMessage.getDbLang(), 0) ? orderHeaderCn : orderHeaderEn);
        Sheet sheet = writer.getSheet();
        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 40 * 256);
        sheet.setColumnWidth(3, 20 * 256);
        sheet.setColumnWidth(4, 20 * 256);
        sheet.setColumnWidth(5, 20 * 256);
        sheet.setColumnWidth(6, 60 * 256);
        sheet.setColumnWidth(7, 80 * 256);
        sheet.setColumnWidth(9, 10 * 256);
        writer.merge(orderHeaderList.size() - 1, "待发货订单信息整理");
        writer.writeRow(orderHeaderList);
        //配送方式
        String[] dvyTypes = {"快递", "无需快递", "同城配送"};
        createDropDownList(sheet, dvyTypes, 2, 50000, 1, 1);
        //快递公司
        List<Delivery> deliveryList = deliveryService.list(new LambdaQueryWrapper<>());
        if (CollUtil.isNotEmpty(deliveryList)) {
            String[] dvyName = new String[deliveryList.size()];
            for (int i = 0; i < deliveryList.size(); i++) {
                dvyName[i] = deliveryList.get(i).getDvyName();
            }
            createDropDownList(sheet, dvyName, 2, 50000, 2, 2);
        }
        //写入数据
        int row = 1;
        for (UnDeliveryOrderExcelVO orderExcel : orderExcelList) {
            List<OrderItem> orderItemList = orderExcel.getOrderItemList();
            int firstRow = row + 1;
            int lastRow = row + orderItemList.size();
            int col = -1;
            //订单号
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, orderExcel.getOrderNumber());
            //配送方式
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, DeliveryType.getDescription(orderExcel.getDvyType()));
            //快递公司名称
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, orderExcel.getDvyName());
            //快递单号
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, orderExcel.getDvyFlowId());
            //收货人姓名
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, orderExcel.getReceiver());
            //收货人手机号
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, BooleanUtil.isFalse(permission)? PhoneUtil.hideBetween(orderExcel.getMobile()).toString():orderExcel.getMobile());
            //收货地址
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, orderExcel.getReceivingAddr());
            int prodNameCol = ++col;
            int skuNameCol = ++col;
            int countCol = ++col;
            for (OrderItem orderItem : orderItemList) {
                row++;
                //商品名称
                writer.writeCellValue(prodNameCol, row, orderItem.getProdName());
                //sku名称
                writer.writeCellValue(skuNameCol, row, orderItem.getSkuName());
                //商品待发货数量
                writer.writeCellValue(countCol, row, orderItem.getChangeNum());
            }
        }
        PoiExcelUtil.writeExcel(response, writer);
    }

    @Override
    public String exportOrderExcel(MultipartFile file, Long shopId, List<UnDeliveryOrderExcelVO> orderList) throws Exception {
        try(InputStream inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream)) {
            //读取工作表
            Sheet sheet = workbook.getSheetAt(0);
            int allRow = sheet.getPhysicalNumberOfRows();
            //无内容
            if (allRow <= MIN_ROW) {
                throw new KimoShopBindException("kimo.document.no.content");
            }
            // * 2.1不是Excel文件，直接返回提示信息
            if (!(file.getOriginalFilename().contains("xls") || file.getOriginalFilename().contains("xlsx"))) {
                // 格式错误
                throw new KimoShopBindException("kimo.file.error");
            }
            //进行数据校验
            List<Delivery> deliveryList = deliveryService.list(new LambdaQueryWrapper<>());
            Map<String, Long> deliveryCompanyMap = deliveryList.stream().collect(Collectors.toMap(Delivery::getDvyName, Delivery::getDvyId));
            int dvyType = 0;
            int step = 1;
            for (int i = 2; i < allRow; i += step) {
                UnDeliveryOrderExcelVO unDeliveryOrderExcelVO = new UnDeliveryOrderExcelVO();
                int trueStep = 1;
                try {
                    trueStep = checkExcel(sheet, i, dvyType, unDeliveryOrderExcelVO, shopId, deliveryCompanyMap, trueStep);
                    step = trueStep;
                } catch (Exception e) {
                    //错误信息提示
                    return "第" + (i + 1) + "行数据有错误," + e.getMessage();
                }
                orderList.add(unDeliveryOrderExcelVO);
            }
            return null;
        } catch (Exception e) {
            return e.getMessage();
        }

    }

    /**
     * 如果需要合并的话，就合并
     */
    private void mergeIfNeed(ExcelWriter writer, int firstRow, int lastRow, int firstColumn, int lastColumn, Object content) {
        if (lastRow - firstRow > 0 || lastColumn - firstColumn > 0) {
            writer.merge(firstRow, lastRow, firstColumn, lastColumn, content, false);
        } else {
            writer.writeCellValue(firstColumn, firstRow, content);
        }

    }

    private int checkExcel(Sheet sheet, int i, int dvyType, UnDeliveryOrderExcelVO unDeliveryOrderExcelVO, Long shopId, Map<String, Long> deliveryCompanyMap, int trueStep) {
        Row row = sheet.getRow(i);
        int cellRow = 0;
        String orderNumber = PoiExcelUtil.getRowValue(row, cellRow, false);
        if (Objects.isNull(orderNumber) || orderNumber.length() == 0) {
            //订单不存在
            throw new KimoShopBindException("kimo.order.no.exist");
        }
        UnDeliveryOrderExcelVO order = orderService.getOrderAndOrderItemsByOrderNumberAndShopId(orderNumber, shopId);
        if (Objects.isNull(order)) {
            // 订单不存在
            throw new KimoShopBindException("kimo.order.no.exist");
        }
        for (OrderItem orderItem : order.getOrderItemList()) {
            if (Objects.equals(orderItem.getStatus(),0) ||(!Objects.equals(orderItem.getProdCount(),orderItem.getStatus()) && !Objects.equals(orderItem.getStatus(),-1))){
                //如果有订单项发货了，则不允许批量发货
                throw new KimoShopBindException("kimo.order.is.delivery");
            }
        }
        String deliveryType = PoiExcelUtil.getRowValue(row, ++cellRow, false);
        if (Objects.isNull(deliveryType) || deliveryType.length() == 0) {
            //请选择配送方式
            throw new KimoShopBindException("kimo.product.dvy.type");
        } else if (Objects.equals(deliveryType, DeliveryType.NO_EXPRESS.getDesc())) {
            dvyType = DeliveryType.NO_EXPRESS.getValue();
        } else if (Objects.equals(deliveryType, DeliveryType.SAME_CITY.getDesc())) {
            dvyType = DeliveryType.SAME_CITY.getValue();
        } else if (Objects.equals(deliveryType, DeliveryType.EXPRESS.getDesc())) {
            dvyType = DeliveryType.EXPRESS.getValue();
        }
        if (Objects.equals(dvyType, DeliveryType.SAME_CITY.getValue()) && !Objects.equals(order.getDvyType(), DeliveryType.SAME_CITY.getValue())) {
            //当前订单不支持同城配送
            throw new KimoShopBindException("kimo.order.same.city.error3");
        }
        String dvyName = PoiExcelUtil.getRowValue(row, ++cellRow, false);
        String dvyFlowId = PoiExcelUtil.getRowValue(row, ++cellRow, false);
        if (Objects.equals(deliveryType, DeliveryType.EXPRESS.getDesc())) {
            //只有快递需要校验快递信息
            if (Objects.isNull(dvyName) || dvyName.length() == 0) {
                //快递公司名称不能为空
                throw new KimoShopBindException("kimo.order.no.dvyName");
            }
            if (Objects.isNull(dvyFlowId) || dvyFlowId.length() < FLOW_ID_MIN || dvyFlowId.length() > FLOW_ID_MAX) {
                //快递单号格式错误
                throw new KimoShopBindException("kimo.order.delivery.number");
            }
            unDeliveryOrderExcelVO.setDvyFlowId(dvyFlowId);
            unDeliveryOrderExcelVO.setDvyName(dvyName);
            unDeliveryOrderExcelVO.setDvyId(deliveryCompanyMap.get(dvyName));
        }
        unDeliveryOrderExcelVO.setOrderNumber(orderNumber);
        unDeliveryOrderExcelVO.setDvyType(dvyType);
        List<OrderItem> orderItems = order.getOrderItemList();

        //已经全部发货的就不加入到待发货订单项
        orderItems.removeIf(orderItem -> Objects.equals(orderItem.getStatus(), 0));
        if (orderItems.size() > 1) {
            trueStep = orderItems.size();
        }
        unDeliveryOrderExcelVO.setOrderItemList(orderItems);
        return trueStep;
    }

    /**
     * 创建下拉列表选项
     *
     * @param sheet    所在Sheet页面
     * @param values   下拉框的选项值
     * @param firstRow 起始行（从0开始）
     * @param lastRow  终止行（从0开始）
     * @param firstCol 起始列（从0开始）
     * @param lastCol  终止列（从0开始）
     */
    private void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();

        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);

        // Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
    }
}
